package org.nzd.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

public class Demo {
    //我是第二位程序员修改了代码
	//我又修改了代码
	static Scanner input = new Scanner(System.in);
	//连接数据库的相关信息
	private static String url="jdbc:mysql://47.100.96.196:3306/nzd1002?useSSL=false&useUnicode=true&characterEncoding=utf-8";
	private static String user="root";
	private static String password="qwe123!@#";

	public static void main(String[] args) {
		mainMenu();
	}

	// 项目主菜单
	private static void mainMenu() {
		while (true) {
			System.out.println("----------学生管理系统------------");
			System.out.println("1、添加学生信息");
			System.out.println("2、查询学生信息");
			System.out.println("3、修改学生信息");
			System.out.println("4、删除学生信息");
			System.out.println("0、退出系统");
			System.out.println("-----------------------------");

			int menu=getInt("请选择功能菜单<1、2、3、4、0>:");
			
			switch (menu) {
			case 1:
				addStudent();
				break;
			case 2:
				selectStudent();
				break;
			case 3:
				updateStudent();
				break;
			case 4:
				deleteStudent();
				break;
			case 0:
				System.exit(0);
				break;

			default:
				break;
			}
		}
	}

	private static void deleteStudent() {
		try {
			//1、导入驱动包
			//2、加载驱动类
			Class.forName("com.mysql.jdbc.Driver");
			//3、创建链接对象
			Connection connection = DriverManager.getConnection(url, user, password);
			//4、创建命令栏对象
			Statement sttm = connection.createStatement();
			//5、创建sql语句
			int id=getInt("请输入要删除的学号:");
			String sql="delete from student where id="+id;
			//6、开始执行sql语句
			int i = sttm.executeUpdate(sql);
			if (i>0) {
				System.out.println("删除成功！！！");
			}else{
				System.out.println("删除失败！！！");
			}
			//7、关闭连接对象
			connection.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}

	private static void updateStudent() {
		try {
			//1、导入驱动包
			//2、加载驱动类
			Class.forName("com.mysql.jdbc.Driver");
			//3、创建链接对象
			Connection connection = DriverManager.getConnection(url, user, password);
			//4、创建命令栏对象
			Statement sttm = connection.createStatement();
			//5、创建sql语句
			int id=getInt("请输入需要修改的学号:");
			String name=getString("请重新输入姓名:");
			String pwd=getString("请重新输入密码:");
			String sex=getString("请重新输入性别:");
			String tel=getString("请重新输入电话:");
			String address=getString("请重新输入地址:");
			
			String sql="update student set name='"+name+"',pwd='"+pwd+"',sex='"+
					sex+"',tel='"+tel+"',address='"+address+"' where id="+id;
			//6、开始执行sql语句
			int i = sttm.executeUpdate(sql);
			if (i>0) {
				System.out.println("修改成功！！！");
			}else{
				System.out.println("修改失败！！！");
			}
			//7、关闭连接对象
			connection.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	//查询所有学生信息
	private static void selectStudent() {
		// TODO Auto-generated method stub
		try {
			//1、导入驱动包
			//2、加载驱动类
			Class.forName("com.mysql.jdbc.Driver");
			//3、创建链接对象
			Connection connection = DriverManager.getConnection(url, user, password);
			//4、创建命令栏对象
			Statement sttm = connection.createStatement();
			//5、创建sql语句
			String sql="select * from student";
			//6、开始执行查询sql
			ResultSet resultSet = sttm.executeQuery(sql);
			//开始读取结果集所有内容
			System.out.println("学号\t姓名\t密码\t性别\t电话\t地址");
			while (resultSet.next()) {
				System.out.print(resultSet.getObject(1)+"\t");
				System.out.print(resultSet.getObject(2)+"\t");
				System.out.print(resultSet.getObject(3)+"\t");
				System.out.print(resultSet.getObject(4)+"\t");
				System.out.print(resultSet.getObject(5)+"\t");
				System.out.println(resultSet.getObject(6)+"\t");
			}
			
			//7、关闭连接对象
			resultSet.close();
			connection.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private static void addStudent() {
		try {
			//1、导入驱动包
			//2、加载驱动类
			Class.forName("com.mysql.jdbc.Driver");
			//3、创建链接对象
			Connection connection = DriverManager.getConnection(url, user, password);
			//4、创建命令栏对象
			Statement sttm = connection.createStatement();
			//5、创建sql语句
			int id=getInt("请输入学号:");
			String name=getString("请输入姓名:");
			String pwd=getString("请输入密码:");
			String sex=getString("请输入性别:");
			String tel=getString("请输入电话:");
			String address=getString("请输入地址:");
			String sql="insert into student values("+id+",'"+name+"','"+pwd+"','"+
					sex+"','"+tel+"','"+address+"')";
			//6、开始执行sql语句
			int i = sttm.executeUpdate(sql);
			if (i>0) {
				System.out.println("添加成功！！！");
			}else{
				System.out.println("添加失败！！！");
			}
			//7、关闭连接对象
			connection.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 输出内容并获取用户输入的整形
	public static int getInt(String msg) {
		System.out.print(msg);
		return input.nextInt();
	}

	// 输出内容并获取用户输入的字符串
	public static String getString(String msg) {
		System.out.print(msg);
		return input.next();
	}
}
